#!/usr/bin/env python3

import pymysql
import sys
import logging
import typing as tp

from .item import *
from .config import config

__all__ = ["DatabaseOperation"]

logger = logging.getLogger(__name__)

def _log_and_exit(err):
  logger.error(err)
  sys.exit(1)

commands = {
  "login":     "select * from users where username=\"{uname}\" and password=\"{psword}\"",
  "user info": "select * from {table} where name=\"{uname}\"",
  "courses":   "select * from Course",
  "student":   "select * from Student where id=\"{sid}\"",
  "students":  "select * from Student",
  "get_stu_and_score":    "select s.*, sc.score from Student s, Course c, CourseSelection sc "
                          "where s.id = sc.s_id and sc.c_id = c.id and c.id = \"{cid}\" order by "
                          "sc.score desc",
  "get_course_and_score": "select c.*, sc.score from Student s, Course c, CourseSelection sc "
                          "where s.id = sc.s_id and sc.c_id = c.id and s.id = \"{sid}\" order by "
                          "sc.score desc",
  "del_stu_from_course":  "delete from CourseSelection where s_id=\"{sid}\" and c_id=\"{cid}\" ",
  "alter_score_for_course": "update CourseSelection set score=\"{sc}\" where s_id=\"{sid}\" "
                            "and c_id=\"{cid}\"",
  "ins_student_to_course":  "insert into CourseSelection values(\"{cid}\", \"{sid}\", \"{sc}\")",
  "get_greater_table":      "select s.*, c.*, sc.score from Student s, CourseSelection sc, Course "
                            "c where s.id = sc.s_id and c.id = sc.c_id and sc.score >= (select avg(score) "
                            "from CourseSelection savg where savg.s_id = sc.s_id) order by sc.s_id;"
}

class DatabaseOperation:
  __config = config
  try:
    __db = pymysql.connect(**__config.model_dump(), autocommit=True)
    __cursor = __db.cursor(cursor=pymysql.cursors.DictCursor)
  except pymysql.Error as e:
    _log_and_exit(e)

  def execute(self, command, **kwargs):
    DatabaseOperation.__cursor.execute(commands[command].format(**kwargs))
    return DatabaseOperation.__cursor.fetchall()

  def login(self, username: str, password: str):
    try:
      user_login, = self.execute("login", uname=username, psword=password)
      user_info, = self.execute("user info",
                                uname=username,
                                table=user_login["type"])
      return user_deserialize(user_login, user_info)
    except:
      return None

  def get_target_student(self, sid: str):
    return self.execute("student", sid=sid)

  def get_student_score_in_course(self, cid: str):
    return self.execute("get_stu_and_score", cid=cid)

  def get_sc_for_student(self, sid: str):
    return self.execute("get_course_and_score", sid=sid)

  def get_courses(self) -> tp.List[Course]:
    return course_deserialize(self.execute("courses"))

  def get_students(self):
    return self.execute("students")

  def del_stu_from_course(self, sid: str, cid: str) -> bool:
    try:
      self.execute("del_stu_from_course", sid=sid, cid=cid)
    except:
      return False
    return True

  def alter_score_for_course(self, sid: str, score: float, cid: str) -> bool:
    try:
      self.execute("alter_score_for_course", sc=score, sid=sid, cid=cid)
    except Exception as error:
      return False
    return True

  def add_student_into_course(self, sid: str, cid: str, score: float):
    try:
      self.execute("ins_student_to_course", cid=cid, sid=sid, sc=score)
    except:
      return False
    return True

  def get_all_greater(self):
    return self.execute("get_greater_table")
